SET NOCOUNT ON
USE Northgale
go
CREATE OR ALTER PROCEDURE static_search_5_tvp
       @orderid     int          = NULL,
       @status      char(1)      = NULL,
       @fromdate    date         = NULL,
       @todate      date         = NULL,
       @custid      nchar(5)     = NULL,
       @custname    nvarchar(40) = NULL,
       @city        nvarchar(25) = NULL,
       @region      nvarchar(15) = NULL,
       @prodid      int          = NULL,
       @prodname    nvarchar(40) = NULL,
       @employeetbl dbo.intlist_tbltype READONLY AS

-- Is there data in the table?
DECLARE @hastable bit = IIF(EXISTS (SELECT * FROM @employeetbl), 1, 0)

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder, o.EmployeeID
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.Status = @status OR @status IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)

  AND  (o.EmployeeID IN (SELECT val FROM @employeetbl) OR
       @hastable = 0)
ORDER  BY o.OrderID
OPTION (RECOMPILE)
go
-- This version uses a UDF. This gives better estimates than string_split,
-- thanks to interleaved execution. (The UDF call is run before the rest
-- of the query is compiled.)
CREATE OR ALTER PROCEDURE static_search_5_udf
       @orderid     int           = NULL,
       @status      char(1)       = NULL,
       @fromdate    date          = NULL,
       @todate      date          = NULL,
       @custid      nchar(5)      = NULL,
       @custname    nvarchar(40)  = NULL,
       @city        nvarchar(25)  = NULL,
       @region      nvarchar(15)  = NULL,
       @prodid      int           = NULL,
       @prodname    nvarchar(40)  = NULL,
       @employeecsv nvarchar(MAX) = NULL AS

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder, o.EmployeeID
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.Status = @status OR @status IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)

  AND  (o.EmployeeID IN (SELECT n FROM dbo.intlist_to_tbl(@employeecsv)) OR
        @employeecsv IS NULL)
ORDER  BY o.OrderID
OPTION (RECOMPILE)
go
CREATE OR ALTER PROCEDURE static_search_5_string_split
       @orderid     int           = NULL,
       @status      char(1)       = NULL,
       @fromdate    date          = NULL,
       @todate      date          = NULL,
       @custid      nchar(5)      = NULL,
       @custname    nvarchar(40)  = NULL,
       @city        nvarchar(25)  = NULL,
       @region      nvarchar(15)  = NULL,
       @prodid      int           = NULL,
       @prodname    nvarchar(40)  = NULL,
       @employeecsv nvarchar(MAX) = NULL AS

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder, o.EmployeeID
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.Status = @status OR @status IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)

  AND  (o.EmployeeID IN 
          (SELECT convert(int, value) FROM string_split(@employeecsv, ',')) OR
        @employeecsv IS NULL)
ORDER  BY o.OrderID
OPTION (RECOMPILE)
go
CREATE OR ALTER PROCEDURE static_search_5_tmp
       @orderid     int           = NULL,
       @status      char(1)       = NULL,
       @fromdate    date          = NULL,
       @todate      date          = NULL,
       @custid      nchar(5)      = NULL,
       @custname    nvarchar(40)  = NULL,
       @city        nvarchar(25)  = NULL,
       @region      nvarchar(15)  = NULL,
       @prodid      int           = NULL,
       @prodname    nvarchar(40)  = NULL,
       @employeecsv nvarchar(MAX) = NULL AS

-- Use this temp table for the data, be sure to do
-- UPDATE STATISTICS.
CREATE TABLE #emps (empid int NOT NULL PRIMARY KEY)
IF @employeecsv IS NOT NULL
BEGIN
   INSERT #emps(empid)
      SELECT n FROM dbo.intlist_to_tbl(@employeecsv)
   UPDATE STATISTICS #emps
END

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder, o.EmployeeID
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.Status = @status OR @status IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)

  -- Extra conditions for the new parameters.
  AND  (o.EmployeeID IN (SELECT empid FROM #emps) OR @employeecsv IS NULL)
ORDER  BY o.OrderID
OPTION (RECOMPILE)
go
-- Compare the plans for the three different parameters.
-- 901, 902 and 903 have in total 64 orders, that is few.
DECLARE @tbl dbo.intlist_tbltype
INSERT @tbl (val) VALUES (901), (902), (903)
EXEC static_search_5_tvp @employeetbl = @tbl
EXEC static_search_5_udf @employeecsv = '901,902,903'
EXEC static_search_5_string_split @employeecsv = '901,902,903'
EXEC static_search_5_tmp @employeecsv = '901,902,903'
go
-- Try again, now with an employee that has thousands of orders.
DECLARE @tbl dbo.intlist_tbltype
INSERT @tbl (val) VALUES (337)
EXEC static_search_5_tvp @employeetbl = @tbl
--EXEC static_search_5_udf @employeecsv = '337'
--EXEC static_search_5_string_split @employeecsv = '337'
EXEC static_search_5_tmp @employeecsv = '337'
go


-- Try static_search_5_udf with different compat levels.
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 110
go
EXEC static_search_5_udf @employeecsv = '901,902,903'
go
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130
go
EXEC static_search_5_udf @employeecsv = '901,902,903'
go
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170
go

-- Try running the procedure with the temp table without
-- UPDATE STATISTICS.
CREATE OR ALTER PROCEDURE static_search_5_tmp_noupdstat
       @orderid     int          = NULL,
       @status      char(1)      = NULL,
       @fromdate    date         = NULL,
       @todate      date         = NULL,
       @custid      nchar(5)     = NULL,
       @custname    nvarchar(40) = NULL,
       @city        nvarchar(25) = NULL,
       @region      nvarchar(15) = NULL,
       @prodid      int          = NULL,
       @prodname    nvarchar(40) = NULL,
       @employeetbl dbo.intlist_tbltype READONLY AS

DECLARE @hastable bit = 0

-- Use this temp table for the data, here we don't update
-- statistics which gives some interesting effects.
CREATE TABLE #emps (empid int NOT NULL PRIMARY KEY)
IF EXISTS (SELECT * FROM @employeetbl)
BEGIN
   SET @hastable = 1
   INSERT #emps(empid)
      SELECT val FROM @employeetbl
   -- UPDATE STATISTICS #emps
END

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder, o.EmployeeID
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.Status = @status OR @status IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)

  AND  (o.EmployeeID IN (SELECT empid FROM #emps) OR @hastable = 0)
ORDER  BY o.OrderID
OPTION (RECOMPILE)
go
DECLARE @tbl dbo.intlist_tbltype
INSERT @tbl (val) VALUES (901), (902), (903)
EXEC static_search_5_tmp_noupdstat @employeetbl = @tbl
go
DECLARE @tbl dbo.intlist_tbltype
INSERT @tbl (val) VALUES (337)
EXEC static_search_5_tmp_noupdstat @employeetbl = @tbl
